﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TTTTBHYT.VO;

namespace TTTTBHYT.DAO
{
    public class Mau29DAO
    {
        private dbConnection conn;
        Random rnd = new Random();
        public Mau29DAO()
        {
            conn = new dbConnection();
        }
        public DataTable SearchTenBV(string maBV)
        {
            string query = string.Format("select * from  " + TTTTBHYT.Properties.Settings.Default.DATASMS + ".Dbo.DMBV "
            + " where MA_TINH + MA_BV = @mabv");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@mabv", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(maBV);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchBySoKCB(string KeySL)
        {
            string query = string.Format("select * from MAU29 "
            + " where KeySL = @keysl");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(KeySL);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchBySoKCBFromBHXH(string soKCB)
        {
            string query = string.Format("SELECT 0 As Chon, a.STT, a.Madvi, a.Sobhxh, a.SoKCB, b.Ho, b.Ten, "
            + " a.TuNgay, a.DenNgay, b.NgaySinh, b.GioiTinh, b.Namsinh, CASE WHEN c.InTenDvi<>0 "
            + " THEN c.Tentat ELSE b.DiaChi END As DiaChi,  case when a.Ma_Tinh = '' then '56' else a.Ma_Tinh end as Ma_Tinh, a.Ma_BV,d.Ten_BV, a.Tinh_Den, a.NgayMua, "
            + " a.NgayGiao, a.NgayCap, a.LanCap, a.Macl, a.MaDT, a.NgayGiam, a.GhiChu, a.KeySL, "
            + " a.KeyGH, a.Keyp, a.Dain, a.MaPB, a.UserCode, a.Ngayin  "
            + " FROM MISBHXH_SOTHE_nt.Dbo.INTHE_DTL a LEFT JOIN " + TTTTBHYT.Properties.Settings.Default.DATASMS  + ".Dbo.HOSOCN b "
            + " ON a.Sobhxh=b.Sobhxh  LEFT JOIN " + TTTTBHYT.Properties.Settings.Default.DATASMS + ".Dbo.DMDONVI c ON a.Madvi=c.Madvi "
            + " LEFT join " + TTTTBHYT.Properties.Settings.Default.DATASMS + ".dbo.DMBV d on d.Ma_Tinh = a.Ma_tinh and d.ma_bv = a.Ma_bv "
            + " WHERE a.sokcb = @soKCB  AND (a.Hethan = 0 OR Ngaygiam IS NOT NULL)"
            + " and denngay >= '" + DateTime.Now.Month + "/" + DateTime.Now.Day + "/" + DateTime.Now.Year + "'");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@soKCB", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(soKCB);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchBV(string condition)
        {
            string query = string.Format("SELECT Dmbv.MA_BV, Dmbv.TEN_BV, Dmbv.MA_TINH,Dmbv.Ten_Tinh"
            + " FROM  DMBV Dmbv "
            + " WHERE  1=1 " + condition + " order by Dmbv.MA_BV");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchAll()
        {
            string query = string.Format("select * from MAU29 order by hoten");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable Search(string condition)
        {
            string query = string.Format("select * from MAU29 where 1 = 1 " + condition + " order by hoten");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public MAU29VO InsertRecord(MAU29VO Mau29VO)
        {
            string SoKCB;
            string query = string.Format("INSERT INTO [TTTTBHYT_NT].[dbo].[MAU29]([KEYSL], [SOKCB], [HOTEN], [NGAYSINH], "
            + " [GIOITINH], [DIACHI], [MADT], [MA_BV], [MA_TINH],[TEN_BV], [TUNGAY], [DENNGAY], [MA_BV_DT], [MA_TINH_DT],[TEN_BV_DT]," 
            + " [TUNGAYDT], [DENNGAYDT], [SONGAY],[SONGAYTT], [LOAIDT], [HANGBV],[MAICD],[CHUANDOAN], [TTNV], [LOAIGT], [THEBHYT], [TUYENKHAM],"
            + " [KETLUAN], [THANHTOAN], [QUY],[NGAYCT],[SOCHUNGTU], [XETNGHIEM], [HA], [THUOC], [MAU], [THUTHUAT], [VATTU], [TIENKHAM],"
            + " [PHAUTHUATCAO], [VATTUCAO], [LAMSANGCAO], [THUOCK], [XETNGHIEMKCB], [HAKCB], [THUOCKCB], [MAUKCB],"
            + " [THUTHUATKCB], [VATTUKCB], [TIENKHAMKCB], [PHAUTHUATCAOKCB], [VATTUCAOKCB], [LAMSANGCAOKCB], [THUOCKKCB],"
            + " [XETNGHIEMLYDO], [HALYDO], [THUOCLYDO], [MAULYDO], [THUTHUATLYDO], [VATTULYDO], [TIENKHAMLYDO],"
            + " [PHAUTHUATCAOLYDO], [VATTUCAOLYDO], [LAMSANGCAOLYDO], [THUOCKLYDO], [TIENTHANHTOAN], [NBTHANHTOAN],[BHXHTHANHTOAN],[BHXHNDX],"
            + " [KHAC],[KHACKCB],[KHACLYDO],[NGAYTT])"
            + " VALUES(@keysl,@sokcb, @hoten, @ngaysinh, @gioitinh, @diachi,@madt, @mabv, @matinh,@tenbv, @tungay, @denngay, @mabvdt,"
            + " @matinhdt,@tenbvdt, @tungaydt,@denngaydt, @songay,@songaytt, @loaidt, @hangbv,@maicd,@chuandoan, @ttnv, @loaigt, @thebhyt, @tuyenkham, @ketluan, @thanhtoan,"
            + " @quy,@ngayct,@sochungtu, @xetnghiem, @ha, @thuoc, @mau, @thuthuat, @vattu, @tienkham, @phauthuatcao, @vattucao, @lamsangcao, @thuock,"
            + " @xetnghiemkcb, @hakcb, @thuockcb, @maukcb, @thuthuatkcb, @vattukcb, @tienkhamkcb, @phauthuatcaokcb, @vattucaokcb, @lamsangcaokcb, @thuockkcb,"
            + " @xetnghiemlydo, @halydo, @thuoclydo, @maulydo, @thuthuatlydo, @vattulydo, @tienkhamlydo, @phauthuatcaolydo, @vattucaolydo, @lamsangcaolydo, @thuocklydo,"
            + " @tienthanhtoan, @nbthanhtoan, @bhxhthanhtoan,@ngoaidx,@khac,@khackcb,@khaclydo,@ngaytt)");


            SqlParameter[] sqlParameters = new SqlParameter[73];
            sqlParameters[0] = new SqlParameter("@sokcb", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(Mau29VO.SoKCB);

            SoKCB = sqlParameters[0].Value.ToString();

            sqlParameters[1] = new SqlParameter("@hoten", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(Mau29VO.HoTen);

            sqlParameters[2] = new SqlParameter("@ngaysinh", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(Mau29VO.NgaySinh);
           
            sqlParameters[3] = new SqlParameter("@gioitinh", SqlDbType.Int);
            sqlParameters[3].Value = Convert.ToInt32(Mau29VO.GioiTinh);
            //dat          
            sqlParameters[4] = new SqlParameter("@diachi", SqlDbType.NVarChar);
            sqlParameters[4].Value = Convert.ToString(Mau29VO.DiaChi);

            sqlParameters[5] = new SqlParameter("@madt", SqlDbType.NVarChar);
            sqlParameters[5].Value = Convert.ToString(Mau29VO.MaDT);

            sqlParameters[6] = new SqlParameter("@mabv", SqlDbType.NVarChar);
            sqlParameters[6].Value = Convert.ToString(Mau29VO.MaBV);

            sqlParameters[7] = new SqlParameter("@matinh", SqlDbType.NVarChar );
            sqlParameters[7].Value = Convert.ToString(Mau29VO.MaTinh);

            sqlParameters[8] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[8].Value = Convert.ToString(Mau29VO.TuNgay);

            sqlParameters[9] = new SqlParameter("@denngay", SqlDbType.NVarChar);
            sqlParameters[9].Value = Convert.ToString(Mau29VO.DenNgay);

            sqlParameters[10] = new SqlParameter("@mabvdt", SqlDbType.NVarChar );
            sqlParameters[10].Value = Convert.ToString(Mau29VO.MaBVDT);
             
            sqlParameters[11] = new SqlParameter("@matinhdt", SqlDbType.NVarChar);
            sqlParameters[11].Value = Convert.ToString(Mau29VO.MaTinhDT);

            sqlParameters[12] = new SqlParameter("@tungaydt", SqlDbType.NVarChar);
            sqlParameters[12].Value = Convert.ToString(Mau29VO.TuNgayDT);

            sqlParameters[13] = new SqlParameter("@denngaydt", SqlDbType.NVarChar);
            sqlParameters[13].Value = Convert.ToString(Mau29VO.DenNgayDT);

            sqlParameters[14] = new SqlParameter("@songay", SqlDbType.Int);
            sqlParameters[14].Value = Convert.ToString(Mau29VO.SoNgay);

            sqlParameters[15] = new SqlParameter("@loaidt", SqlDbType.Int);
            sqlParameters[15].Value = Convert.ToInt32(Mau29VO.LoaiDT);
            //dat
            sqlParameters[16] = new SqlParameter("@hangbv", SqlDbType.Int);
            sqlParameters[16].Value = Convert.ToInt32(Mau29VO.HangBV);

            sqlParameters[17] = new SqlParameter("@ttnv", SqlDbType.Int);
            sqlParameters[17].Value = Convert.ToInt32(Mau29VO.TTNV);
          
            sqlParameters[18] = new SqlParameter("@loaigt", SqlDbType.Int);
            sqlParameters[18].Value = Convert.ToInt32(Mau29VO.LoaiGT);

            sqlParameters[19] = new SqlParameter("@thebhyt", SqlDbType.Int);
            sqlParameters[19].Value = Convert.ToInt32(Mau29VO.TheBHYT);

            sqlParameters[20] = new SqlParameter("@tuyenkham", SqlDbType.Int);
            sqlParameters[20].Value = Convert.ToInt32(Mau29VO.TuyenKham);

            sqlParameters[21] = new SqlParameter("@ketluan", SqlDbType.Int);
            sqlParameters[21].Value = Convert.ToInt32(Mau29VO.KetLuan);
            //cat            
            sqlParameters[22] = new SqlParameter("@thanhtoan", SqlDbType.Int);
            sqlParameters[22].Value = Convert.ToInt32(Mau29VO.ThanhToan);
             
            sqlParameters[23] = new SqlParameter("@quy", SqlDbType.NVarChar);
            sqlParameters[23].Value = Convert.ToInt32(Mau29VO.Quy);
            //thanh toan
            sqlParameters[24] = new SqlParameter("@xetnghiem", SqlDbType.Int);
            sqlParameters[24].Value = Convert.ToInt32(Mau29VO.XetNghiem);

            sqlParameters[25] = new SqlParameter("@ha", SqlDbType.Int);
            sqlParameters[25].Value = Convert.ToInt32(Mau29VO.Ha);

            sqlParameters[26] = new SqlParameter("@thuoc", SqlDbType.Int);
            sqlParameters[26].Value = Convert.ToInt32(Mau29VO.Thuoc);

            sqlParameters[27] = new SqlParameter("@mau", SqlDbType.Int);
            sqlParameters[27].Value = Convert.ToInt32(Mau29VO.Mau);
            
            sqlParameters[28] = new SqlParameter("@thuthuat", SqlDbType.Int);
            sqlParameters[28].Value = Convert.ToInt32(Mau29VO.Thuthuat);

            sqlParameters[29] = new SqlParameter("@vattu", SqlDbType.Int);
            sqlParameters[29].Value = Convert.ToInt32(Mau29VO.VatTu);

            sqlParameters[30] = new SqlParameter("@tienkham", SqlDbType.Int);
            sqlParameters[30].Value = Convert.ToInt32(Mau29VO.TienKham);

            sqlParameters[31] = new SqlParameter("@phauthuatcao", SqlDbType.Int);
            sqlParameters[31].Value = Convert.ToInt32(Mau29VO.PhauThuatCao);

            sqlParameters[32] = new SqlParameter("@lamsangcao", SqlDbType.Int);
            sqlParameters[32].Value = Convert.ToInt32(Mau29VO.LamSangCao);

            sqlParameters[33] = new SqlParameter("@vattucao", SqlDbType.Int);
            sqlParameters[33].Value = Convert.ToInt32(Mau29VO.VatTuCao);
                     
            sqlParameters[34] = new SqlParameter("@thuocK", SqlDbType.Int);
            sqlParameters[34].Value = Convert.ToInt32(Mau29VO.ThuocK);
            //bhyt
            sqlParameters[35] = new SqlParameter("@xetnghiemkcb", SqlDbType.Int);
            sqlParameters[35].Value = Convert.ToInt32(Mau29VO.XetNghiemKCB);

            sqlParameters[36] = new SqlParameter("@hakcb", SqlDbType.Int);
            sqlParameters[36].Value = Convert.ToInt32(Mau29VO.HaKCB);

            sqlParameters[37] = new SqlParameter("@thuockcb", SqlDbType.Int);
            sqlParameters[37].Value = Convert.ToInt32(Mau29VO.ThuocKCB);

            sqlParameters[38] = new SqlParameter("@maukcb", SqlDbType.Int);
            sqlParameters[38].Value = Convert.ToInt32(Mau29VO.MauKCB);

            sqlParameters[39] = new SqlParameter("@thuthuatkcb", SqlDbType.Int);
            sqlParameters[39].Value = Convert.ToInt32(Mau29VO.ThuthuatKCB);

            sqlParameters[40] = new SqlParameter("@vattukcb", SqlDbType.Int);
            sqlParameters[40].Value = Convert.ToInt32(Mau29VO.VatTuKCB);

            sqlParameters[41] = new SqlParameter("@tienkhamkcb", SqlDbType.Int);
            sqlParameters[41].Value = Convert.ToInt32(Mau29VO.TienKhamKCB);

            sqlParameters[42] = new SqlParameter("@phauthuatcaokcb", SqlDbType.Int);
            sqlParameters[42].Value = Convert.ToInt32(Mau29VO.PhauThuatCaoKCB);

            sqlParameters[43] = new SqlParameter("@lamsangcaokcb", SqlDbType.Int);
            sqlParameters[43].Value = Convert.ToInt32(Mau29VO.LamSangCaoKCB);

            sqlParameters[44] = new SqlParameter("@vattucaokcb", SqlDbType.Int);
            sqlParameters[44].Value = Convert.ToInt32(Mau29VO.VatTuCaoKCB);

            sqlParameters[45] = new SqlParameter("@thuocKkcb", SqlDbType.Int);
            sqlParameters[45].Value = Convert.ToInt32(Mau29VO.ThuocKKCB);
            //LYDO
            sqlParameters[46]= new SqlParameter("@xetnghiemlydo", SqlDbType.NVarChar);
            sqlParameters[46].Value = Convert.ToString(Mau29VO.XetNghiemLyDo);

            sqlParameters[47] = new SqlParameter("@halydo", SqlDbType.NVarChar);
            sqlParameters[47].Value = Convert.ToString(Mau29VO.HaLyDo);

            sqlParameters[48] = new SqlParameter("@thuoclydo", SqlDbType.NVarChar);
            sqlParameters[48].Value = Convert.ToString(Mau29VO.ThuocLyDo);

            sqlParameters[49] = new SqlParameter("@maulydo", SqlDbType.NVarChar);
            sqlParameters[49].Value = Convert.ToString(Mau29VO.MauLyDo);

            sqlParameters[50] = new SqlParameter("@thuthuatlydo", SqlDbType.NVarChar);
            sqlParameters[50].Value = Convert.ToString(Mau29VO.ThuthuatLyDo);

            sqlParameters[51] = new SqlParameter("@vattulydo", SqlDbType.NVarChar);
            sqlParameters[51].Value = Convert.ToString(Mau29VO.VatTuLyDo);

            sqlParameters[52] = new SqlParameter("@tienkhamlydo", SqlDbType.NVarChar);
            sqlParameters[52].Value = Convert.ToString(Mau29VO.TienKhamLyDo);

            sqlParameters[53] = new SqlParameter("@phauthuatcaolydo", SqlDbType.NVarChar);
            sqlParameters[53].Value = Convert.ToString(Mau29VO.PhauThuatCaoLyDo);

            sqlParameters[54] = new SqlParameter("@lamsangcaolydo", SqlDbType.NVarChar);
            sqlParameters[54].Value = Convert.ToString(Mau29VO.LamSangCaoLyDo);

            sqlParameters[55] = new SqlParameter("@vattucaolydo", SqlDbType.NVarChar);
            sqlParameters[55].Value = Convert.ToString(Mau29VO.VatTuCaoLyDo);

            sqlParameters[56] = new SqlParameter("@thuocKlydo", SqlDbType.NVarChar);
            sqlParameters[56].Value = Convert.ToString(Mau29VO.ThuocKLyDo);
           
            sqlParameters[57] = new SqlParameter("@nbthanhtoan", SqlDbType.Int );
            sqlParameters[57].Value = Convert.ToInt32(Mau29VO.NbThanhToan);

            sqlParameters[58] = new SqlParameter("@bhxhthanhtoan", SqlDbType.Int);
            sqlParameters[58].Value = Convert.ToInt32(Mau29VO.BhxhThanhToan);

            sqlParameters[59] = new SqlParameter("@tienthanhtoan", SqlDbType.Int);
            sqlParameters[59].Value = Convert.ToInt32(Mau29VO.TienThanhToan);

            sqlParameters[60] = new SqlParameter("@tenbv", SqlDbType.NVarChar);
            sqlParameters[60].Value = Convert.ToString(Mau29VO.TenBV);

            sqlParameters[61] = new SqlParameter("@tenbvdt", SqlDbType.NVarChar);
            sqlParameters[61].Value = Convert.ToString(Mau29VO.TenBVDT);

            sqlParameters[62] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[62].Value = Convert.ToString(Module.CreateRunninNo(rnd));

            sqlParameters[63] = new SqlParameter("@chuandoan", SqlDbType.NVarChar);
            sqlParameters[63].Value = Convert.ToString(Mau29VO.ChuanDoan);

            sqlParameters[64] = new SqlParameter("@songaytt", SqlDbType.Int);
            sqlParameters[64].Value = Mau29VO.SoNgayTT;

            sqlParameters[65] = new SqlParameter("@sochungtu", SqlDbType.Int);
            sqlParameters[65].Value =Mau29VO.SoChungTu;

            sqlParameters[66] = new SqlParameter("@ngayct", SqlDbType.NVarChar);
            sqlParameters[66].Value = Convert.ToString(Mau29VO.NgayCT);

            sqlParameters[67] = new SqlParameter("@maicd", SqlDbType.Char);
            sqlParameters[67].Value = Convert.ToString(Mau29VO.MaICD);

            sqlParameters[68] = new SqlParameter("@ngoaidx", SqlDbType.Int);
            sqlParameters[68].Value = Mau29VO.BhxhNDX;

            sqlParameters[69] = new SqlParameter("@khac", SqlDbType.Int);
            sqlParameters[69].Value = Convert.ToInt32(Mau29VO.Khac);

            sqlParameters[70] = new SqlParameter("@khacKCB", SqlDbType.Int);
            sqlParameters[70].Value = Convert.ToInt32(Mau29VO.KhacKCB);

            sqlParameters[71] = new SqlParameter("@khacLyDo", SqlDbType.NVarChar);
            sqlParameters[71].Value = Convert.ToString(Mau29VO.KhacLyDo);

            sqlParameters[72] = new SqlParameter("@ngaytt", SqlDbType.NVarChar);
            sqlParameters[72].Value = Convert.ToString(Mau29VO.NgayTT);

            if (conn.executeInsertQuery(query, sqlParameters))
            {
                Mau29VO.KeySL = sqlParameters[62].Value.ToString();
                return Mau29VO;
            }
            else
            {
                return null;
            }
        }
        public bool UpdateRecord(MAU29VO Mau29VO)
        {
            string SoKCB;
            string query = string.Format("UPDATE MAU29 SET "
            + " [HOTEN] = @hoten, [NGAYSINH]=  @ngaysinh, "
            + " [GIOITINH] = @gioitinh, [DIACHI]= @diachi, [MADT] = @madt, [MA_BV]=@mabv, [MA_TINH]=@matinh, [TEN_BV] = @tenbv,"
            + " [TUNGAY] =@tungay , [DENNGAY] = @denngay, [MA_BV_DT] =  @mabvdt, [MA_TINH_DT] = @matinhdt,[TEN_BV_DT] = @tenbvdt," 
            + " [TUNGAYDT] = @tungaydt, [DENNGAYDT]=@denngaydt, [SONGAY]=@songay,[SONGAYTT] = @songaytt, [LOAIDT]=@loaidt, [HANGBV]= @hangbv,[MAICD] = @maicd ,[CHUANDOAN] = @chuandoan,"
            + " [TTNV] = @ttnv , [LOAIGT]=@loaigt, [THEBHYT]=@thebhyt, [TUYENKHAM]=@tuyenkham,"
            + " [KETLUAN] = @ketluan, [THANHTOAN] = @thanhtoan, [QUY] = @quy,[NGAYCT] = @ngayct, [SOCHUNGTU] = @sochungtu, [XETNGHIEM] = @xetnghiem, [HA] =@ha ,"
            + " [THUOC] = @thuoc , [MAU] =  @mau, [THUTHUAT]= @thuthuat, [VATTU]=@vattu, [TIENKHAM]=@tienkham,"
            + " [PHAUTHUATCAO]=@phauthuatcao, [VATTUCAO]=@vattucao, [LAMSANGCAO]=@lamsangcao, [THUOCK]=@thuock,"
            + " [XETNGHIEMKCB]=@xetnghiemkcb, [HAKCB]=@hakcb, [THUOCKCB]=@thuockcb, [MAUKCB]=@maukcb,"
            + " [THUTHUATKCB]=@thuthuatkcb, [VATTUKCB]= @vattukcb, [TIENKHAMKCB]=@tienkhamkcb, [PHAUTHUATCAOKCB]=@phauthuatcaokcb,"
            + " [VATTUCAOKCB] = @vattucaokcb, [LAMSANGCAOKCB] = @lamsangcaokcb, [THUOCKKCB]=@thuockkcb,"
            + " [XETNGHIEMLYDO]=@xetnghiemlydo, [HALYDO]=@halydo, [THUOCLYDO]=@thuoclydo, [MAULYDO]=@maulydo, [THUTHUATLYDO]= @thuthuatlydo,"
            + " [VATTULYDO] = @vattulydo, [TIENKHAMLYDO] = @tienkhamlydo,"
            + " [PHAUTHUATCAOLYDO] = @phauthuatcaolydo, [VATTUCAOLYDO]=@vattucaolydo, [LAMSANGCAOLYDO] = @lamsangcaolydo, [THUOCKLYDO] = @thuocklydo,"
            + " [TIENTHANHTOAN] = @tienthanhtoan, [NBTHANHTOAN] = @nbthanhtoan,[BHXHTHANHTOAN] = @bhxhthanhtoan,[BHXHNDX] = @ngoaidx,"
            + " [KHAC]= @khac,[KHACKCB]=@khackcb,[KHACLYDO]=@khaclydo,[NGAYTT] = @ngaytt "
            + " WHERE KEYSL = @keysl");

            SqlParameter[] sqlParameters = new SqlParameter[73];
            sqlParameters[0] = new SqlParameter("@sokcb", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(Mau29VO.SoKCB);

            SoKCB = sqlParameters[0].Value.ToString();

            sqlParameters[1] = new SqlParameter("@hoten", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(Mau29VO.HoTen);

            sqlParameters[2] = new SqlParameter("@ngaysinh", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(Mau29VO.NgaySinh);

            sqlParameters[3] = new SqlParameter("@gioitinh", SqlDbType.Int);
            sqlParameters[3].Value = Convert.ToInt32(Mau29VO.GioiTinh);
            //dat          
            sqlParameters[4] = new SqlParameter("@diachi", SqlDbType.NVarChar);
            sqlParameters[4].Value = Convert.ToString(Mau29VO.DiaChi);

            sqlParameters[5] = new SqlParameter("@madt", SqlDbType.NVarChar);
            sqlParameters[5].Value = Convert.ToString(Mau29VO.MaDT);

            sqlParameters[6] = new SqlParameter("@mabv", SqlDbType.NVarChar);
            sqlParameters[6].Value = Convert.ToString(Mau29VO.MaBV);

            sqlParameters[7] = new SqlParameter("@matinh", SqlDbType.NVarChar);
            sqlParameters[7].Value = Convert.ToString(Mau29VO.MaTinh);

            sqlParameters[8] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[8].Value = Convert.ToString(Mau29VO.TuNgay);

            sqlParameters[9] = new SqlParameter("@denngay", SqlDbType.NVarChar);
            sqlParameters[9].Value = Convert.ToString(Mau29VO.DenNgay);

            sqlParameters[10] = new SqlParameter("@mabvdt", SqlDbType.NVarChar);
            sqlParameters[10].Value = Convert.ToString(Mau29VO.MaBVDT);

            sqlParameters[11] = new SqlParameter("@matinhdt", SqlDbType.NVarChar);
            sqlParameters[11].Value = Convert.ToString(Mau29VO.MaTinhDT);

            sqlParameters[12] = new SqlParameter("@tungaydt", SqlDbType.NVarChar);
            sqlParameters[12].Value = Convert.ToString(Mau29VO.TuNgayDT);

            sqlParameters[13] = new SqlParameter("@denngaydt", SqlDbType.NVarChar);
            sqlParameters[13].Value = Convert.ToString(Mau29VO.DenNgayDT);

            sqlParameters[14] = new SqlParameter("@songay", SqlDbType.Int);
            sqlParameters[14].Value = Convert.ToString(Mau29VO.SoNgay);

            sqlParameters[15] = new SqlParameter("@loaidt", SqlDbType.Int);
            sqlParameters[15].Value = Convert.ToInt32(Mau29VO.LoaiDT);
            //dat
            sqlParameters[16] = new SqlParameter("@hangbv", SqlDbType.Int);
            sqlParameters[16].Value = Convert.ToInt32(Mau29VO.HangBV);

            sqlParameters[17] = new SqlParameter("@ttnv", SqlDbType.Int);
            sqlParameters[17].Value = Convert.ToInt32(Mau29VO.TTNV);

            sqlParameters[18] = new SqlParameter("@loaigt", SqlDbType.Int);
            sqlParameters[18].Value = Convert.ToInt32(Mau29VO.LoaiGT);

            sqlParameters[19] = new SqlParameter("@thebhyt", SqlDbType.Int);
            sqlParameters[19].Value = Convert.ToInt32(Mau29VO.TheBHYT);

            sqlParameters[20] = new SqlParameter("@tuyenkham", SqlDbType.Int);
            sqlParameters[20].Value = Convert.ToInt32(Mau29VO.TuyenKham);

            sqlParameters[21] = new SqlParameter("@ketluan", SqlDbType.Int);
            sqlParameters[21].Value = Convert.ToInt32(Mau29VO.KetLuan);
            //cat            
            sqlParameters[22] = new SqlParameter("@thanhtoan", SqlDbType.Int);
            sqlParameters[22].Value = Convert.ToInt32(Mau29VO.ThanhToan);

            sqlParameters[23] = new SqlParameter("@quy", SqlDbType.NVarChar);
            sqlParameters[23].Value = Convert.ToInt32(Mau29VO.Quy);
            //thanh toan
            sqlParameters[24] = new SqlParameter("@xetnghiem", SqlDbType.Int);
            sqlParameters[24].Value = Convert.ToInt32(Mau29VO.XetNghiem);

            sqlParameters[25] = new SqlParameter("@ha", SqlDbType.Int);
            sqlParameters[25].Value = Convert.ToInt32(Mau29VO.Ha);

            sqlParameters[26] = new SqlParameter("@thuoc", SqlDbType.Int);
            sqlParameters[26].Value = Convert.ToInt32(Mau29VO.Thuoc);

            sqlParameters[27] = new SqlParameter("@mau", SqlDbType.Int);
            sqlParameters[27].Value = Convert.ToInt32(Mau29VO.Mau);

            sqlParameters[28] = new SqlParameter("@thuthuat", SqlDbType.Int);
            sqlParameters[28].Value = Convert.ToInt32(Mau29VO.Thuthuat);

            sqlParameters[29] = new SqlParameter("@vattu", SqlDbType.Int);
            sqlParameters[29].Value = Convert.ToInt32(Mau29VO.VatTu);

            sqlParameters[30] = new SqlParameter("@tienkham", SqlDbType.Int);
            sqlParameters[30].Value = Convert.ToInt32(Mau29VO.TienKham);

            sqlParameters[31] = new SqlParameter("@phauthuatcao", SqlDbType.Int);
            sqlParameters[31].Value = Convert.ToInt32(Mau29VO.PhauThuatCao);

            sqlParameters[32] = new SqlParameter("@lamsangcao", SqlDbType.Int);
            sqlParameters[32].Value = Convert.ToInt32(Mau29VO.LamSangCao);

            sqlParameters[33] = new SqlParameter("@vattucao", SqlDbType.Int);
            sqlParameters[33].Value = Convert.ToInt32(Mau29VO.VatTuCao);

            sqlParameters[34] = new SqlParameter("@thuocK", SqlDbType.Int);
            sqlParameters[34].Value = Convert.ToInt32(Mau29VO.ThuocK);
            //bhyt
            sqlParameters[35] = new SqlParameter("@xetnghiemkcb", SqlDbType.Int);
            sqlParameters[35].Value = Convert.ToInt32(Mau29VO.XetNghiemKCB);

            sqlParameters[36] = new SqlParameter("@hakcb", SqlDbType.Int);
            sqlParameters[36].Value = Convert.ToInt32(Mau29VO.HaKCB);

            sqlParameters[37] = new SqlParameter("@thuockcb", SqlDbType.Int);
            sqlParameters[37].Value = Convert.ToInt32(Mau29VO.ThuocKCB);

            sqlParameters[38] = new SqlParameter("@maukcb", SqlDbType.Int);
            sqlParameters[38].Value = Convert.ToInt32(Mau29VO.MauKCB);

            sqlParameters[39] = new SqlParameter("@thuthuatkcb", SqlDbType.Int);
            sqlParameters[39].Value = Convert.ToInt32(Mau29VO.ThuthuatKCB);

            sqlParameters[40] = new SqlParameter("@vattukcb", SqlDbType.Int);
            sqlParameters[40].Value = Convert.ToInt32(Mau29VO.VatTuKCB);

            sqlParameters[41] = new SqlParameter("@tienkhamkcb", SqlDbType.Int);
            sqlParameters[41].Value = Convert.ToInt32(Mau29VO.TienKhamKCB);

            sqlParameters[42] = new SqlParameter("@phauthuatcaokcb", SqlDbType.Int);
            sqlParameters[42].Value = Convert.ToInt32(Mau29VO.PhauThuatCaoKCB);

            sqlParameters[43] = new SqlParameter("@lamsangcaokcb", SqlDbType.Int);
            sqlParameters[43].Value = Convert.ToInt32(Mau29VO.LamSangCaoKCB);

            sqlParameters[44] = new SqlParameter("@vattucaokcb", SqlDbType.Int);
            sqlParameters[44].Value = Convert.ToInt32(Mau29VO.VatTuCaoKCB);

            sqlParameters[45] = new SqlParameter("@thuocKkcb", SqlDbType.Int);
            sqlParameters[45].Value = Convert.ToInt32(Mau29VO.ThuocKKCB);
            //LYDO
            sqlParameters[46] = new SqlParameter("@xetnghiemlydo", SqlDbType.NVarChar);
            sqlParameters[46].Value = Convert.ToString(Mau29VO.XetNghiemLyDo);

            sqlParameters[47] = new SqlParameter("@halydo", SqlDbType.NVarChar);
            sqlParameters[47].Value = Convert.ToString(Mau29VO.HaLyDo);

            sqlParameters[48] = new SqlParameter("@thuoclydo", SqlDbType.NVarChar);
            sqlParameters[48].Value = Convert.ToString(Mau29VO.ThuocLyDo);

            sqlParameters[49] = new SqlParameter("@maulydo", SqlDbType.NVarChar);
            sqlParameters[49].Value = Convert.ToString(Mau29VO.MauLyDo);

            sqlParameters[50] = new SqlParameter("@thuthuatlydo", SqlDbType.NVarChar);
            sqlParameters[50].Value = Convert.ToString(Mau29VO.ThuthuatLyDo);

            sqlParameters[51] = new SqlParameter("@vattulydo", SqlDbType.NVarChar);
            sqlParameters[51].Value = Convert.ToString(Mau29VO.VatTuLyDo);

            sqlParameters[52] = new SqlParameter("@tienkhamlydo", SqlDbType.NVarChar);
            sqlParameters[52].Value = Convert.ToString(Mau29VO.TienKhamLyDo);

            sqlParameters[53] = new SqlParameter("@phauthuatcaolydo", SqlDbType.NVarChar);
            sqlParameters[53].Value = Convert.ToString(Mau29VO.PhauThuatCaoLyDo);

            sqlParameters[54] = new SqlParameter("@lamsangcaolydo", SqlDbType.NVarChar);
            sqlParameters[54].Value = Convert.ToString(Mau29VO.LamSangCaoLyDo);

            sqlParameters[55] = new SqlParameter("@vattucaolydo", SqlDbType.NVarChar);
            sqlParameters[55].Value = Convert.ToString(Mau29VO.VatTuCaoLyDo);

            sqlParameters[56] = new SqlParameter("@thuocKlydo", SqlDbType.NVarChar);
            sqlParameters[56].Value = Convert.ToString(Mau29VO.ThuocKLyDo);

            sqlParameters[57] = new SqlParameter("@nbthanhtoan", SqlDbType.Int);
            sqlParameters[57].Value = Convert.ToInt32(Mau29VO.NbThanhToan);

            sqlParameters[58] = new SqlParameter("@bhxhthanhtoan", SqlDbType.Int);
            sqlParameters[58].Value = Convert.ToInt32(Mau29VO.BhxhThanhToan);

            sqlParameters[59] = new SqlParameter("@tienthanhtoan", SqlDbType.Int);
            sqlParameters[59].Value = Convert.ToInt32(Mau29VO.TienThanhToan);

            sqlParameters[60] = new SqlParameter("@tenbv", SqlDbType.NVarChar);
            sqlParameters[60].Value = Convert.ToString(Mau29VO.TenBV);

            sqlParameters[61] = new SqlParameter("@tenbvdt", SqlDbType.NVarChar);
            sqlParameters[61].Value = Convert.ToString(Mau29VO.TenBVDT);

            sqlParameters[62] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[62].Value = Convert.ToString(Mau29VO.KeySL);

            sqlParameters[63] = new SqlParameter("@chuandoan", SqlDbType.NVarChar);
            sqlParameters[63].Value = Convert.ToString(Mau29VO.ChuanDoan);

            sqlParameters[64] = new SqlParameter("@songaytt", SqlDbType.Int);
            sqlParameters[64].Value = Mau29VO.SoNgayTT;

            sqlParameters[65] = new SqlParameter("@sochungtu", SqlDbType.Int);
            sqlParameters[65].Value = Mau29VO.SoChungTu;

            sqlParameters[66] = new SqlParameter("@ngayct", SqlDbType.NVarChar);
            sqlParameters[66].Value = Convert.ToString(Mau29VO.NgayCT);

            sqlParameters[67] = new SqlParameter("@maicd", SqlDbType.Char);
            sqlParameters[67].Value = Convert.ToString(Mau29VO.MaICD);

            sqlParameters[68] = new SqlParameter("@ngoaidx", SqlDbType.Int);
            sqlParameters[68].Value = Mau29VO.BhxhNDX;

            sqlParameters[69] = new SqlParameter("@khac", SqlDbType.Int);
            sqlParameters[69].Value = Convert.ToInt32(Mau29VO.Khac);

            sqlParameters[70] = new SqlParameter("@khacKCB", SqlDbType.Int);
            sqlParameters[70].Value = Convert.ToInt32(Mau29VO.KhacKCB);

            sqlParameters[71] = new SqlParameter("@khacLyDo", SqlDbType.NVarChar);
            sqlParameters[71].Value = Convert.ToString(Mau29VO.KhacLyDo);

            sqlParameters[72] = new SqlParameter("@ngaytt", SqlDbType.NVarChar);
            sqlParameters[72].Value = Convert.ToString(Mau29VO.NgayTT);

            if (conn.executeInsertQuery(query, sqlParameters))
            {
                return true;
            }
            else
            {
                return false ;
            }
        }
        public bool DeleteRecord(string KeySL)
        {
            string query = string.Format("Delete MAU29 Where keysl = @keysl");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(KeySL);
            return conn.executeInsertQuery(query, sqlParameters);

        }
    }
}
